﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Simhopp.Common
{
    /// <summary>
    /// Static class for performing common sql-transactions such as insert, update and remove
    /// </summary>
    public static class MyDbConnection
    {
        private static SqlConnection sqlConnection;

        /// <summary>
        /// Sets the connection-string, i.e. the database connection parameters
        /// </summary>
        /// <param name="connectionString"></param>
        public static void SetConnectionString(string connectionString)
        {
            try
            {
                sqlConnection = new SqlConnection(connectionString);
            }
            catch (Exception e)
            {
                MyLogger.Error("Could not create sql-connection from passed string - " + e.Message);
            }
        }

        /// <summary>
        /// Executes an insert-transaction
        /// </summary>
        /// <param name="commandString">The sql-query to be executed</param>
        /// <returns>The index of the inserted row (provided that the sql-query contains such a question)</returns>
        public static int ExecuteInsert(string commandString)
        {
            if (sqlConnection.State != ConnectionState.Open)
                        sqlConnection.Open();

            int returnValue = -1;
            try
            {
                using (var command = new SqlCommand(commandString, sqlConnection))
                {
                    returnValue = (int)command.ExecuteScalar();
                }
            }
            catch (Exception e)
            {
                MyLogger.Error("Could not perform insert query to database - " + e.Message);
            }
            finally
            {
                sqlConnection.Close();
            }
            return returnValue;
        }

        /// <summary>
        /// Executes an update-transaction
        /// </summary>
        /// <param name="commandString">The sql-query to be executed</param>
        public static bool ExecuteUpdate(string commandString)
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();

            try
            {
                using (var command = new SqlCommand(commandString, sqlConnection))
                {
                    command.ExecuteScalar();
                }
                sqlConnection.Close();
                return true;
            }
            catch (Exception e)
            {
                MyLogger.Error("Could not perform update query to database - " + e.Message);
            }
            finally
            {
                sqlConnection.Close();
            }
            return false;
        }

        /// <summary>
        /// Reads all rows in an sql-query
        /// </summary>
        /// <param name="selectString">The sql-select-query to be performed</param>
        /// <returns>A list of dictionary containing all the fields from the table/the passed query</returns>
        public static List<Dictionary<String, Object>> ReadFromTable(string selectString)
        {
            List<Dictionary<String, Object>> objectsInQuery;

            if (sqlConnection.State != ConnectionState.Open)
                        sqlConnection.Open();

            using (var command = new SqlCommand(selectString, sqlConnection))
            {
                objectsInQuery = new List<Dictionary<String, Object>>();
                try
                {
                    var reader = command.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            var dictionaryInRow = new Dictionary<String, Object>();
                            for (int fieldIndex = 0; fieldIndex < reader.FieldCount; fieldIndex++)
                            {
                                dictionaryInRow.Add(reader.GetName(fieldIndex), reader.GetValue(fieldIndex));
                            }
                            objectsInQuery.Add(dictionaryInRow);
                        }
                    }
                    reader.Close();
                }
                catch (Exception e)
                {
                    MyLogger.Error("Could not perform select query to database - " + e.Message);
                }
                finally
                {
                    sqlConnection.Close();
                }
            }

            return objectsInQuery;
        }

        public static void OpenConnection()
        {
            if (sqlConnection.State != ConnectionState.Open)
                sqlConnection.Open();
            
        }
    }
}
